package com.cmwa.scbp.utils;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.lang.StringUtils;

public class SQLUtil {
	
	public static final int SPLIT_COUNT = 1000;
	
	/**
	 * 构建INSQL
	 * @param ids
	 * @param count
	 * @param field
	 * @return
	 */
	public static String buildOracleSQLIn(List<?> ids, int count, String field) {
		count = Math.min(count, 1000);
		int len = ids.size();
		int size = len % count;
		if (size == 0) {
			size = len / count;
		} else {
			size = (len / count) + 1;
		}
		StringBuilder builder = new StringBuilder();
		for (int i = 0; i < size; i++) {
			int fromIndex = i * count;
			int toIndex = Math.min(fromIndex + count, len);
			String objId = StringUtils.defaultIfEmpty(StringUtils.join(ids.subList(fromIndex, toIndex), "','"), "");
			if (i != 0) {
				builder.append(" OR ");
			}
			builder.append(field).append(" IN ('").append(objId).append("')");
		}
		return StringUtils.defaultIfEmpty(builder.toString(), field + " IN ('')");
	}
	
	
	
	/**
	 * 构建IN SQL 多个字段拼接(适合数据库多个字段组合查询) 如：(FIELD1,FIELD2) IN(('A','B'),('C','D') ......) OR (FIELD1,FIELD2) IN(('A','B'),('C','D')
	 * @param ids  为一个字符串集合,单个值为"A,B"
	 * @param count  一个IN支持最多参数个数
	 * @param field  数据库字段名  如果是联合查询，格式为"FIELD1,FIELD2"
	 * @return
	 */
	public static String buildOracleSQLInMany(List<String> ids, int count, String field) {
		Iterator<String> it = ids.iterator();  
		List<String> newList  = new  ArrayList<String>();
	
		while(it.hasNext()){
			newList.add("("+splitInResult(it.next())+")");
		}
		String olgField = field;
		field = "("+field+")";
		count = Math.min(count, 1000);
		int len = ids.size();
		int size = len % count;
		if (size == 0) {
			size = len / count;
			
		} else {
			size = (len / count) + 1;
		}
		StringBuilder builder = new StringBuilder();
		for (int i = 0; i < size; i++) {
			int fromIndex = i * count;
			int toIndex = Math.min(fromIndex + count, len);
			String objId = StringUtils.defaultIfEmpty(StringUtils.join(newList.subList(fromIndex, toIndex), ","), "");
			if (i != 0) {
				builder.append(" OR ");
			}
			builder.append(field).append(" IN (").append(objId).append(")");
		}
		return StringUtils.defaultIfEmpty(builder.toString(), field + " IN (("+splitInResult(olgField)+"))");
	}
	
	/**
	 * 把"a,b,c"转化成"'a','b','c'"
	 * 
	 * @param str
	 * @return
	 */
	public static String splitInResult(String parameter) {
		if (StringUtils.isBlank(parameter)) {
			return "";
		}
		String str[] = parameter.split(",");
		boolean flag = false;
		StringBuilder builder = new StringBuilder();
		for (int i = 0; i < str.length; i++) {
			if (StringUtils.isNotBlank(str[i].trim())) {
				flag = true;
				builder.append("'").append(str[i].trim()).append("',");
			}
		}
		if (flag) {
			return builder.substring(0, builder.length() - 1);
		}
		return "";
	}
	
	public static void main(String[] args) {
		List<String> ids =  new ArrayList<String>();
//		for(int i = 0 ; i < 20 ; i++){
//			ids.add("securid" + i + ",orgid" + i);
//		}
		String sql = SQLUtil.buildOracleSQLInMany(ids, SQLUtil.SPLIT_COUNT, "SECUR_ID, ORG_ID");
		System.out.println(sql);
	}

}
